﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="DocumentsService.cs" company="Microsoft">
//   Microsoft
// </copyright>
// <summary>
//   Domain service that exposes methods to connect to SQL Server Denali
// </summary>
// Copyright (c) Microsoft Corporation. All rights reserved.
// 
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
// EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES 
// OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
// ----------------------------------------------------------------------------------
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
// --------------------------------------------------------------------------------------------------------------------

namespace MySemanticSearch.Web.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Globalization;
    using System.Linq;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using System.Transactions;
    using System.Web.Management;

    using MySemanticSearch.Web.Model;
    using MySemanticSearch.Web.QueryBuilder;

    /// <summary>
    /// Domain service that exposes methods to connect to SQL Server Denali
    /// </summary>
    [EnableClientAccess]
    public class DocumentsService : DomainService
    {
        /// <summary>
        /// Private dictionary that returns the icons for the document list
        /// </summary>
        private static readonly IList<string> DocumentExtensions = new List<string> { "docx", "doc", "pdf" };

        /// <summary>
        /// Maximum number of decimals for tags and documents' score
        /// </summary>
        private const int RoundScore = 5;

        /// <summary>
        /// Gets tags with highest score in the table.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <param name="tagCount">
        /// The tag count.
        /// </param>
        /// <returns>
        /// Tags with highest score in the table.
        /// </returns>
        public IDictionary<string, double> GetTags(string sqlInstance, string database, string fileTable, int tagCount)
        {
            var tags = new Dictionary<string, double>();

            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(fileTable) || tagCount == 0)
            {
                return tags;
            }

            using (var connection = SqlServerManagement.ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetTags, fileTable);
                using (var command = new SqlCommand(query, connection))
                {
                    try
                    {
                        command.Parameters.AddWithValue("@TagCount", tagCount);

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                tags[reader.GetString(0)] = Math.Round(reader.GetDouble(1), RoundScore);
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatTagCount, tagCount);
                        var errorMessage = App_Data.ExceptionMessages.GetTagsError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return tags;
        }

        /// <summary>
        /// Upload a document to the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <param name="stream">
        /// The stream.
        /// </param>
        /// <param name="fileName">
        /// The filename.
        /// </param>
        /// <returns>
        /// True. (because DomainService methods cannot return void)
        /// </returns>
        public bool UploadDocument(string sqlInstance, string database, string fileTable, byte[] stream, string fileName)
        {
            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(fileTable) || string.IsNullOrEmpty(fileName) || stream == null)
            {
                return false;
            }

            using (var scope = new TransactionScope())
            {
                using (var connection = SqlServerManagement.ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
                {
                    var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.UploadDocument, fileTable);
                    using (var command = new SqlCommand(query, connection))
                    {
                        try
                        {
                            command.Parameters.AddWithValue("@filestream", stream);
                            command.Parameters.AddWithValue("@filename", fileName);

                            command.ExecuteNonQuery();

                            scope.Complete();
                        }
                        catch
                        {
                            var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                                String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                                String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFilename, fileName);
                            var errorMessage = App_Data.ExceptionMessages.UploadDocumentError;

                            throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                        }
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Downloads a document from the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <param name="docId">
        /// The filename.
        /// </param>
        /// <returns>
        /// Document data stream
        /// </returns>
        public byte[] DownloadDocument(string sqlInstance, string database, string fileTable, string docId)
        {
            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(fileTable) || string.IsNullOrEmpty(docId))
            {
                return new byte[0];
            }

            using (var connection = SqlServerManagement.ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetDocument, fileTable);
                using (var command = new SqlCommand(query, connection))
                {
                    try
                    {
                        command.Parameters.AddWithValue("@DocId", docId);

                        return (byte[])command.ExecuteScalar();
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFilename, docId);
                        var errorMessage = App_Data.ExceptionMessages.DownloadDocumentError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }
        }

        /// <summary>
        /// Get all documents from the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="filetable">
        /// The filetable.
        /// </param>
        /// <returns>
        /// Documents in the database.
        /// </returns>
        public IList<Document> GetDocuments(string sqlInstance, string database, string filetable)
        {
            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(filetable))
            {
                return new List<Document>();
            }

            var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetDocuments, filetable);
            using (var command = new SqlCommand(query))
            {
                return GetDocumentsCommand(sqlInstance, database, command);
            }
        }
        
        /// <summary>
        /// Queries the data base using the conditions in the input string
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file table.
        /// </param>
        /// <param name="input">
        /// The input.
        /// </param>
        /// <returns>
        /// A dictionary of documents that match the given conditions
        /// </returns>
        public IList<Document> GetSearchResults(string sqlInstance, string database, string fileTable, string input)
        {
            var parser = new QueryParser(new ConditionBuilder(), fileTable);
            var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetSearchResults, fileTable) + " " + parser.ParseQuery(input);
            using (var command = new SqlCommand(query))
            {
                try
                {
                    return GetDocumentsCommand(sqlInstance, database, command);
                }
                catch (Exception)
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable);
                    var errorMessage = App_Data.ExceptionMessages.GetTagsDocumentError;

                    throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                }
            }
        }

        /// <summary>
        /// Delete a document from the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file Table.
        /// </param>
        /// <param name="docId">
        /// The filename.
        /// </param>
        /// <returns>
        /// True. (because DomainService methods cannot return void)
        /// </returns>
        public bool DeleteDocument(string sqlInstance, string database, string fileTable, string docId)
        {
            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(fileTable) || string.IsNullOrEmpty(docId))
            {
                return false;
            }

            using (var scope = new TransactionScope())
            {
                using (var connection = SqlServerManagement.ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
                {
                    var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.DeleteDocument, fileTable);
                    using (var command = new SqlCommand(query))
                    {
                        try
                        {
                            command.Connection = connection;
                            command.Parameters.AddWithValue("@DocId", docId);

                            command.ExecuteNonQuery();

                            scope.Complete();
                        }
                        catch
                        {
                            var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                                String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                                String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFilename, docId);
                            var errorMessage = App_Data.ExceptionMessages.DeleteDocumentError;

                            throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                        }
                    }
                }
            }

            return true;
        }
        
        /// <summary>
        /// Given a tag this method queries the database for a list of documents associated with the tag along with the score
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="filetable">
        /// The filetable.
        /// </param>
        /// <param name="tag">
        /// The given tag
        /// </param>
        /// <returns>
        /// A dictionary with the documents as keys and the scores as values
        /// </returns>
        public IList<Document> GetDocumentsByTag(string sqlInstance, string database, string filetable, string tag)
        {
            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(filetable) || string.IsNullOrEmpty(tag))
            {
                return new List<Document>();
            }

            var query = String.Format(App_Data.SqlQueries.GetDocumentsByTag, filetable);
            using (var command = new SqlCommand(query))
            {
                try
                {
                    command.Parameters.AddWithValue("@tag", tag);

                    return GetDocumentsByTagCommand(sqlInstance, database, command);
                }
                catch (Exception)
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                        String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatTag, tag) + Environment.NewLine +
                        String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, filetable);
                    var errorMessage = App_Data.ExceptionMessages.GetTagsDocumentError;

                    throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                }
            }
        }

        /// <summary>
        /// Retrieves a set of tags along with their scores related to a given set of documents
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="fileTable">
        /// The file table.
        /// </param>
        /// <param name="docIds">
        /// The doc names.
        /// </param>
        /// <param name="tagCount">
        /// The tag count.
        /// </param>
        /// <returns>
        /// A IDictionary with tags as keys and score as values
        /// </returns>
        /// <exception cref="SqlExecutionException">
        /// </exception>
        public IDictionary<string, double> GetTagsForDocuments(string sqlInstance, string database, string fileTable, List<string> docIds, int tagCount)
        {
            if (string.IsNullOrEmpty(sqlInstance) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(fileTable) || tagCount == 0)
            {
                return new Dictionary<string, double>();
            }

            var tags = new Dictionary<string, double>();

            using (var connection = SqlServerManagement.ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                var ids = string.Empty;
                if (docIds.Count > 1)
                {
                    ids = docIds.Aggregate(ids, (current, docId) => string.Equals(docIds.Last(), docId) ? string.Concat(current, "'", docId, "'") : string.Concat(current, "'", docId, "', "));
                }
                else if (docIds.Count() == 1)
                {
                    ids = string.Concat("'", docIds.First(), "'");
                }

                var query = String.Format(CultureInfo.InvariantCulture, App_Data.SqlQueries.GetTagsFromDocuments, fileTable, ids);
                using (var command = new SqlCommand(query))
                {
                    try
                    {
                        command.Connection = connection;
                        command.Parameters.AddWithValue("@TagCount", tagCount);

                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                tags[reader.GetString(0)] = Math.Round(reader.GetDouble(1), RoundScore);
                            }
                        }
                    }
                    catch
                    {
                        var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatFileTable, fileTable) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDocumentNames, ids);
                        var errorMessage = App_Data.ExceptionMessages.GetDocumentTagsError;

                        throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                    }
                }
            }

            return tags;
        }

        /// <summary>
        /// Overrides default OnError behaviour when an exception occurs
        /// </summary>
        /// <param name="errorInfo">
        /// The error info.
        /// </param>
        protected override void OnError(DomainServiceErrorInfo errorInfo)
        {
            if (errorInfo == null)
            {
                return;
            }

            var serviceException = errorInfo.Error;
            if (serviceException == null)
            {
                return;
            }

            throw new DomainException(serviceException.Message);
        }

        /// <summary>
        /// Executes a SQL command that retrives documents from the database.
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql Instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="command">
        /// The SQL command to retrive documents.
        /// </param>
        /// <returns>
        /// The documents selected in the command.
        /// </returns>
        private static IList<Document> GetDocumentsCommand(string sqlInstance, string database, SqlCommand command)
        {
            var documents = new List<Document>();

            using (var connection = SqlServerManagement.ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                try
                {
                    command.Connection = connection;

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var document = new Document
                                {
                                    Created = reader.GetString(0),
                                    Modified = reader.GetString(1),
                                    Accessed = reader.GetString(2),
                                    Guid = reader.GetSqlGuid(3).ToString(),
                                    ReadOnly = reader.GetSqlBoolean(4).Value,
                                    Hidden = reader.GetSqlBoolean(5).Value,
                                    FileName = reader.GetString(6),
                                    FileType = reader.GetString(7)
                                };

                            if (!DocumentExtensions.Contains(document.FileType))
                            {
                                document.FileType = "empty";
                            }

                            documents.Add(document);
                        }
                    }
                }
                catch
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatSQLCommand, command.CommandText);
                    var errorMessage = App_Data.ExceptionMessages.GetDocumentsError;

                    throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                }
            }

            return documents;
        }

        /// <summary>
        /// Executes a SQL command that retrives documents related to a particular tag from the database, and the relevance of each document to that tag
        /// </summary>
        /// <param name="sqlInstance">
        /// The sql instance.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="command">
        /// The command.
        /// </param>
        /// <returns>
        /// Returns a dictionary with the document's title as key and the score as value
        /// </returns>
        private static IList<Document> GetDocumentsByTagCommand(string sqlInstance, string database, SqlCommand command)
        {
            var documents = new List<Document>();

            using (var connection = SqlServerManagement.ConnectServerInstance(sqlInstance, database).ConnectionContext.SqlConnectionObject)
            {
                try
                {
                    command.Connection = connection;

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var document = new Document
                            {
                                Created = reader.GetString(0),
                                Modified = reader.GetString(1),
                                Accessed = reader.GetString(2),
                                Guid = reader.GetSqlGuid(3).ToString(),
                                ReadOnly = reader.GetSqlBoolean(4).Value,
                                Hidden = reader.GetSqlBoolean(5).Value,
                                FileName = reader.GetString(6),
                                FileType = reader.GetString(7),
                                Score = Math.Round(reader.GetFloat(8), RoundScore)
                            };

                            if (!DocumentExtensions.Contains(document.FileType))
                            {
                                document.FileType = "empty";
                            }

                            documents.Add(document);
                        }
                    }
                }
                catch
                {
                    var errorInformation = String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatDatabase, database) + Environment.NewLine +
                            String.Format(CultureInfo.CurrentCulture, App_Data.ExceptionMessages.FormatSQLCommand, command.CommandText);
                    var errorMessage = App_Data.ExceptionMessages.GetDocumentsError;

                    throw new SqlExecutionException(errorMessage + Environment.NewLine + errorInformation);
                }
            }

            return documents;
        }
    }
}
